package dao;

import model.CategoryModel;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


public class CategoryDAO {
    /**
     * 根据类目ID获取类目信息
     * @param categoryId
     * @return
     */
    public CategoryModel getCategoryByCategoryId(int categoryId){
        String sql = "select * from category where categoryid=? ";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DbConnection.getInstance().getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,categoryId);
            rs = ps.executeQuery();
            while(rs.next()){
                CategoryModel m = new CategoryModel();
                m.setCategoryId(rs.getInt("categoryid"));
                m.setCategoryName(rs.getString("categoryname"));
                m.setOrderNo(rs.getInt("orderno"));
                return m;
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(rs!=null){
                try{
                    rs.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(ps!=null){
                try{
                    ps.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
    /**
     * 查询的有类目
     * @return
     */
    public List<CategoryModel> findAllCategory(){
        List<CategoryModel> cateList = new ArrayList<CategoryModel>();
        String sql = "select * from category order by orderno asc ";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DbConnection.getInstance().getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                CategoryModel m = new CategoryModel();
                m.setCategoryId(rs.getInt("categoryid"));
                m.setCategoryName(rs.getString("categoryname"));
                m.setOrderNo(rs.getInt("orderno"));
                cateList.add(m);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(rs!=null){
                try{
                    rs.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(ps!=null){
                try{
                    ps.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
        return cateList;
    }


}
